/**
 * File with database functions
 */  

var db = new air.SQLConnection();
var bArtExcist = false;
var bAlbExcist = false;

/**
 * Setup DB-file.
 * DB-file is stored in the applicationfolder.
 */
function setupDb() {
	var dbFile = air.File.applicationStorageDirectory.resolvePath('collection.db');
	try{
		db.open(dbFile);
	}
	catch(error) {
		air.trace("DB error:", error.message);  
		air.trace("Details:", error.details);  
	}
}

/**
 * AddRelease
 * Checks are made if artist and album already excist.
 */
function addRelease() {
	var result;
	addArtist(curRelease.artist, curRelease.art_from, '', curRelease.art_mb_id);
	result = executeQuery("select artist_id from artist where name = '" + curRelease.artist +"'");
	art_id = result.data[0].artist_id;
	addAlbum(curRelease.album,curRelease.albumYear,art_id,curRelease.cover,curRelease.alb_mb_id,$('#media').val());
	if (!bAlbExcist) {
		result = executeQuery("select album_id from album where artist_id = '" + art_id + "' and title = '" + curRelease.album + "'");
		alb_id = result.data[0].album_id;
		for (i = 0; i < curRelease.songs.length; i++) {
			addSong(curRelease.songs[i][0], alb_id, curRelease.songs[i][1]);
		}
	}
	else {
		showMessage('Resultaat', '<br/><br/>Album reeds toegevoegd!');
	}
}

/**
 * Add an artist to the database
 * @param {String} name
 * @param {int} a_since
 * @param {int} a_till
 * @param {String} mb_id
 */
function addArtist(name,a_since,a_till,mb_id) {
	var query = "insert into artist (name, active_since, active_till, musicBrainz_id)";
	query    += " values ('"+name+"', '"+a_since+"', '"+a_till+"', '"+mb_id+"')";
	executeQuery(query);
}

/**
 * Add an album to the database
 * @param {String} title
 * @param {int} year
 * @param {int} artist_id
 * @param {String} cover
 * @param {String} mb_id
 */
function addAlbum(title,year,artist_id,cover,mb_id,media) {
	var query = "insert into album (title, year, artist_id, cover, musicBrainz_id,media)";
	query    += " values ('"+title+"', '"+year+"', '"+artist_id+"', '"+cover+"', '"+mb_id+"', '"+media+"')";
	if(executeQuery(query) == null){
		bAlbExcist = true;
	};
}

/**
 * Add the songs to the database
 * @param {String} name
 * @param {int} album_id
 * @param {String} duration
 */
function addSong(name,album_id,duration) {
	var query  = "insert into songs (name, album_id,duration)";
	query     += " values ('"+name+"', '"+album_id+"', '"+duration+"')";
	executeQuery(query);
}

/**
 * Get the releases which start with the given first letter
 * @param {String} firstLetter If empty all releases are retrieved
 * @return {String} result of the query
 */
function getReleaseList(firstLetter){
	var query = "select name, title, cover, album_id, media, year, artist.musicBrainz_id as artist_id ";
	query    += "from artist, album ";
	query    += "where artist.artist_id = album.artist_id and name like '" + firstLetter +"%' ";
	query    += "order by name, year, title, cover";
	return executeQuery(query);
}

/**
 * Getting one release and the songs 
 * @param {int} alb_id
 * @return {String} result of the query
 */ 
function getSelectedRelease(alb_id) {
	var query = "select name, title, cover, year ";
	query    += "from artist, album ";
	query    += "where artist.artist_id = album.artist_id and album_id = '" + alb_id + "'";
	return executeQuery(query);
}

/**
 * Getting the songs of a given release
 * @param {int} alb_id
 * @return {String} result of the query
 */
function getSongs(alb_id) {
	var query = "select song_id, name, duration ";
	query    += "from songs ";
	query    += "where album_id = '" + alb_id + "'";
	return executeQuery(query);
}

/**
 * Update edited songs 
 * @param {int} song_id
 * @param {String} name
 * @param {String} duration
 */ 
function updateSong(song_id,name,duration) {
	if (name != '') {
		var query = "Update songs set name='" + name + "' where song_id = " + song_id;
	}
	else if (duration != ''){
		var query = "Update songs set duration='" + duration + "' where song_id = " + song_id;
	}
	executeQuery(query);
}

/**
 * Updating the year of the album
 * @param {Object} album_id
 * @param {Object} year
 */
function updateAlbum(album_id,year) {
	var query = "Update album set year='" + year + "' where album_id = " + album_id;
	executeQuery(query);
}

/**
 * Delete album and his songs
 * @param {int} id album_id which to delete
 */ 
function deleteAlbum(id){
	var query = "delete from album where album_id = " + id;
	executeQuery(query);
	var query = "delete from songs where album_id = " + id;
	executeQuery(query);
}

/**
 * Getting first chars for filling release combo
 * @return result of the query
 */ 
function getFirstChars() {
	var query = "select substr(name,1,1) as firstLetter ";
	query    += "from artist ";
	query    += "group by substr(name,1,1) ";
	return executeQuery(query);
}

/**  
 * Excecuting Queries
 * @param {String} Query
 * @return result of the query
 */
function executeQuery(Query)  {
	dbQuery = new air.SQLStatement();
	dbQuery.sqlConnection = db;
	dbQuery.text = Query;
	
	try{
		dbQuery.execute();
	}
	catch(error) {
		air.trace("Error querying database: Query: " + Query, error);  
   		air.trace(error.message); 
	}
	return dbQuery.getResult();
}

/**
 * Creating tables if this is the first run of the application
 */
function createTables() {
	var queries = readFile(configFile.sqlFile,false);
	queries = queries.split(';');
	for (indx in queries) {
		executeQuery(queries[indx]);
		air.trace('Created table. Query: ' + queries[indx]);
	}
}
